--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_PAST_AMOUNT_ORG_DEPF.HQL
--    Functions : 表45：存款发生额分机构类型分期限统计表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-12  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_PAST_AMOUNT_ORG_DEPF PARTITION (DATA_DATE = '20180331')
    -- 分期限,机构
    SELECT
         AREA.AREA_CODE_4                                                        AS FIN_ORG_DIST
        ,DEPF.CCY                                                                AS CCY
        ,ORG.ORG_CODE                                                            AS ORG_TYP         -- 机构类型
        ,ORG.ORG_DSCR                                                            AS ORG_DESC        -- 机构类型描述
        ,CASE WHEN TERM.DEPT_TERM_ID IN ('1','2') THEN '1'
              WHEN TERM.DEPT_TERM_ID ='3' THEN '2'
              WHEN TERM.DEPT_TERM_ID ='4' THEN '3'
              WHEN TERM.DEPT_TERM_ID IN ('5','6') THEN '4'
              WHEN TERM.DEPT_TERM_ID ='7' THEN '5'
              WHEN TERM.DEPT_TERM_ID IN ('8','9') THEN '6'
          END                                                                    AS TERM_CODE       -- 期限代码
        ,CASE WHEN TERM.DEPT_TERM_ID IN ('1','2') THEN '3个月(含)以内'
              WHEN TERM.DEPT_TERM_ID ='3' THEN '3个月至6个月(含)'
              WHEN TERM.DEPT_TERM_ID ='4' THEN '6个月至1年(含)'
              WHEN TERM.DEPT_TERM_ID IN ('5','6') THEN '1至3年(含)'
              WHEN TERM.DEPT_TERM_ID ='7' THEN '3至5年(含)'
              WHEN TERM.DEPT_TERM_ID IN ('8','9') THEN '5年以上'
          END                                                                    AS TERM_DESC       -- 期限描述
        ,SUM(COALESCE(DEPF.ACTUAL_BAL,0))/100000000                              AS ACTUAL_BAL      -- 存款余额
        ,SUM(COALESCE(DEPF.BAL_LM,0))/100000000                                  AS BAL_LM          -- 发生额上期末
        ,SUM(COALESCE(DEPF.AMOUNT_YS,0))/100000000                               AS AMOUNT_YS       -- 当年累计发生额
        ,SUM(COALESCE(DEPF.ACTUAL_BAL,0)-COALESCE(DEPF.BAL_LC,0))/100000000      AS BAL_GROW        -- 同比增长
        ,SUM(COALESCE(DEPF.WSUM_BAL,0))/100000000                                AS WSUM_BAL        -- 发生额加权值
        ,SUM(COALESCE(DEPF.WBAL_LM,0))/100000000                                 AS WBAL_LM         -- 发生额加权值上期末
        ,SUM(COALESCE(DEPF.WAMOUNT_YS,0))/100000000                              AS WAMOUNT_YS      -- 当年累计发生额加权值
        ,SUM(COALESCE(DEPF.WBAL_LC,0))/100000000                                 AS WBAL_LC         -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='20180331' AND DEPT_TERM_ID IN ('1','2','3','4','5','6','7','8','9'))DEPF
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON DEPF.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE
    -- 与页面机构表关联
    LEFT JOIN DIMENSION.T_ORG_REPORT ORG ON DEPF.FIN_ORG_NO=ORG.ORG_CODE
    -- 与存款期限表关联
    LEFT JOIN DIMENSION.DIM_DEPOSIT_TERM TERM ON DEPF.TERM_CODE=TERM.DEPT_TERM_ID AND '20180331' BETWEEN TERM.START_DATE AND TERM.END_DATE AND TERM.IS_VALID='1'
    GROUP BY   AREA.AREA_CODE_4
              ,DEPF.CCY
              ,ORG.ORG_CODE
              ,ORG.ORG_DSCR
              ,CASE WHEN TERM.DEPT_TERM_ID IN ('1','2') THEN '1'
                    WHEN TERM.DEPT_TERM_ID ='3' THEN '2'
                    WHEN TERM.DEPT_TERM_ID ='4' THEN '3'
                    WHEN TERM.DEPT_TERM_ID IN ('5','6') THEN '4'
                    WHEN TERM.DEPT_TERM_ID ='7' THEN '5'
                    WHEN TERM.DEPT_TERM_ID IN ('8','9') THEN '6'
                END
              ,CASE WHEN TERM.DEPT_TERM_ID IN ('1','2') THEN '3个月(含)以内'
                    WHEN TERM.DEPT_TERM_ID ='3' THEN '3个月至6个月(含)'
                    WHEN TERM.DEPT_TERM_ID ='4' THEN '6个月至1年(含)'
                    WHEN TERM.DEPT_TERM_ID IN ('5','6') THEN '1至3年(含)'
                    WHEN TERM.DEPT_TERM_ID ='7' THEN '3至5年(含)'
                    WHEN TERM.DEPT_TERM_ID IN ('8','9') THEN '5年以上'
                END

    UNION ALL

    -- 分机构不分期限合计
    SELECT
           AREA.AREA_CODE_4                                                        AS FIN_ORG_DIST
          ,DEPF.CCY                                                                AS CCY
          ,ORG.ORG_CODE                                                            AS ORG_TYP          -- 机构类型
          ,ORG.ORG_DSCR                                                            AS ORG_DESC         -- 机构类型描述
          ,'HJ'                                                                    AS TERM_CODE        -- 期限代码
          ,'合计'                                                                  AS TERM_DESC        -- 期限描述
          ,SUM(COALESCE(DEPF.ACTUAL_BAL,0))/100000000                              AS ACTUAL_BAL       -- 存款发生额
          ,SUM(COALESCE(DEPF.BAL_LM,0))/100000000                                  AS BAL_LM           -- 发生额上期末
          ,SUM(COALESCE(DEPF.AMOUNT_YS,0))/100000000                               AS AMOUNT_YS        -- 当年累计发生额
          ,SUM(COALESCE(DEPF.ACTUAL_BAL,0)-COALESCE(DEPF.BAL_LC,0))/100000000      AS BAL_GROW         -- 同比增长
          ,SUM(COALESCE(DEPF.WSUM_BAL,0))/100000000                                AS WSUM_BAL         -- 发生额加权值
          ,SUM(COALESCE(DEPF.WBAL_LM,0))/100000000                                 AS WBAL_LM          -- 发生额加权值上期末
          ,SUM(COALESCE(DEPF.WAMOUNT_YS,0))/100000000                              AS WAMOUNT_YS       -- 当年累计发生额加权值
          ,SUM(COALESCE(DEPF.WBAL_LC,0))/100000000                                 AS WBAL_LC          -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='20180331')DEPF
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON DEPF.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE
    -- 与页面机构表关联
    LEFT JOIN DIMENSION.T_ORG_REPORT ORG ON DEPF.FIN_ORG_NO=ORG.ORG_CODE
    GROUP BY  AREA.AREA_CODE_4
             ,DEPF.CCY
             ,ORG.ORG_CODE
             ,ORG.ORG_DSCR;